import traceback
import pyautogui
import os
import pyperclip as clip
import json
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
import pypinyin
from bs4 import BeautifulSoup
import random
from jsjj.util.timeUtil import *
from jsjj.util.decryptUtil import *
from jsonpath import jsonpath
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.logUtil import *

modeName = '淘宝_生意参谋_市场_市场排行_店铺'
# 运行模式: 日模式；周模式；月模式
runMode = '月模式'

# 淘宝周模式
# '1': [1226, 352]  第一行
selectWeek = {'1': [1226, 472], '2': [1226, 382], '3': [1226, 412],
              '4': [1226, 442], '5': [1226, 472]}

# 淘宝月模式
selectMonth = {'1月': [1197, 330], '2月': [1274, 330], '3月': [1352, 330],
               '4月': [1197, 365], '5月': [1274, 365], '6月': [1352, 365],
               '7月': [1197, 403], '8月': [1274, 403], '9月': [1352, 403],
               '10月': [1197, 440], '11月': [1274, 440], '12月': [1352, 440]}


# 淘宝的生意参谋:市场-市场排行-【店铺】-高交易
def TopAndLeftMainMenu():
    pyautogui.FAILSAFE = False

    sleep(6)
    # 阿里的生意参谋比较特殊，有缓存，会阻止查询，因此我们默认清除前一个小时的缓存
    # pyautogui.hotkey('ctrl', 'shift', 'delete')  # 再粘贴
    # sleep(4)
    # pyautogui.moveTo(1059, 69, 2)
    # pyautogui.click()

    # fix bug
    pyautogui.moveTo(1662, 226, 2)
    pyautogui.click()

    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')

    # fix bug guanggao
    pyautogui.moveTo(1345, 228, 2)
    pyautogui.click()

    # fix bug 小象
    pyautogui.moveTo(1411, 230, 2)
    pyautogui.click()

    # 顶部市场
    pyautogui.moveTo(900, 187, 4)
    pyautogui.click()
    sleep(10)

    # 市场排行（自动选中：店铺，高交易）
    pyautogui.moveTo(237, 524, 4)
    pyautogui.click()

    # 首次点击后，页面形成，需要较长时间
    sleep(10)

    # 时间还不确定需不需要选择（目前先做按照周的）
    if runMode == '周模式':
        pyautogui.moveTo(1225, 246, 4)
        currentWeekInThisMonth = getWeekOfMonth(datetime.datetime.now().year, datetime.datetime.now().month,
                                                datetime.datetime.now().day)

        # 动态计算出来，默认上周的数据
        currentWeekInThisMonth = currentWeekInThisMonth - 1
        pyautogui.moveTo(selectWeek[str(currentWeekInThisMonth)][0], selectWeek[str(currentWeekInThisMonth)][1], 4)
        pyautogui.click()
        sleep(5)
    elif runMode == '月模式':
        pyautogui.moveTo(1258, 244, 4)

        pyautogui.moveTo(selectMonth[str(datetime.datetime.now().month - 1) + "月"][0],
                         selectMonth[str(datetime.datetime.now().month - 1) + "月"][1], 2)
        pyautogui.click()
        sleep(5)
    elif runMode == '日模式':
        # 其实页面进去，默认就是日模式
        pyautogui.moveTo(1192, 247, 4)
        pyautogui.click()
        sleep(5)


def LoopCat(engine, conn):
    # 解密对象，只需要创建一次
    aes = AesCrypto()

    # 正式运行的sql
    sqlLoop = " select catLeaf,level,cat1,cat2 from 平台_类目表 t where t.channel='淘宝' and t.taobao_sycm_scph_dianpu='0' and t.catLeaf not in('书桌收纳置物架','整理隔板') and t.排除 is null order by t.priority asc "
    # 没有账号，无奈：有时候，只好用2个调试类目进行调试
    # sqlLoop = " select catLeaf,level,cat1,cat2 from 平台_类目表 t where t.channel='淘宝' and t.taobao_sycm_scph_dianpu='0' and t.catLeaf in('书桌收纳置物架','整理隔板') and t.排除 is null order by t.priority asc "

    dfLoop = pd.read_sql(sql=sqlLoop, con=engine)
    for index, row in dfLoop.iterrows():

        # fix bug（广告）
        pyautogui.moveTo(1353, 239, 1)
        pyautogui.click()

        # fix bug（广告）
        pyautogui.moveTo(1344, 227, 1)
        pyautogui.click()

        # 清除network（第一次的是有争议的）
        pyautogui.moveTo(1541, 145, 4)
        pyautogui.click()

        # 生意参谋:模糊查询菜单
        pyautogui.moveTo(430, 245, 6)
        pyautogui.click()

        # 模糊查询textbox
        pyautogui.moveTo(430, 277, 4)
        pyautogui.click()

        # fix bug
        sleep(1)
        pyautogui.hotkey('ctrl', 'a')

        # 数据库中配置的叶子类目菜单
        clip.copy(row['catLeaf'])  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # 选中下拉模糊查询菜单
        if row['catLeaf'] != '沙发':
            if row['level'] is not None and row['level'] != '':
                if row['level'] == '?':
                    LogContinueException('淘宝_生意参谋_市场_市场排行_店铺', engine, conn, "?需要进行处理，否则无法判断菜单的位置", runMode)
                    continue
                pyautogui.moveTo(421, 277 + int(row['level']) * 35, 2)
            else:
                pyautogui.moveTo(421, 277 + 1 * 35, 2)
            pyautogui.click()
        else:
            # 沙发的特殊处理，沙发的菜单，太长了
            sleep(1)
            pyautogui.scroll(-2000)
            sleep(2)
            pyautogui.moveTo(500, 964, 2)
            sleep(2)
            pyautogui.click()
            sleep(1)
            pyautogui.scroll(2000)

        # fix 网络查询返回慢的问题
        sleep(15)

        # filter textbox
        pyautogui.moveTo(1515, 171, 4)
        pyautogui.click()
        sleep(1)
        pyautogui.hotkey('ctrl', 'a')  # 再粘贴

        clip.copy('hotsale')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # 选中第一个文件（fix）
        pyautogui.moveTo(1536, 327, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()

        try:
            aliJsonData = json.loads(value)
            aliJson = aliJsonData['data']
        except Exception as e:
            traceback.print_exc()
            print('【继续】' + str(e))
            LogContinueException('淘宝_生意参谋_市场_市场排行_店铺', engine, conn, str(e), runMode)
            continue

        # 由于json中间结构套结构，原有的没有搞定。因此引入了 jsonpath来灵活处理json的嵌套。
        try:
            jsonDf = {'tradeIndex': jsonpath(aliJson, "$..tradeIndex..value"),
                      'tradeGrowthRange': jsonpath(aliJson, "$..tradeGrowthRange..value"),
                      'payRateIndex': jsonpath(aliJson, "$..payRateIndex..value"),
                      'cateRankId': jsonpath(aliJson, "$..cateRankId..value"),
                      'title': jsonpath(aliJson, "$..shop..title"),
                      'userId': jsonpath(aliJson, "$..userId..value")
                      }
            df = pd.read_json(json.dumps(jsonDf))
        except Exception as e:
            traceback.print_exc()
            LogContinueException('淘宝_生意参谋_市场_市场排行_店铺', engine, conn, str(e), runMode)
            print('【继续】' + str(e))
            continue
        df['statDate'] = datetime.datetime.now()
        df['cat1'] = row['cat1']
        df['cat2'] = row['cat2']
        df['cat3'] = row['catLeaf']
        df['运行模式'] = runMode
        df['统计月'] = ''
        df['统计周'] = ''
        df['统计日'] = ''
        df['统计日周月'] = ''
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        if runMode == '月模式':
            df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
            df['统计日周月'] = df['统计月']
        if runMode == '周模式':
            df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,datetime.datetime.now().day)
            df['统计日周月'] = df['统计周']
        if runMode == '日模式':
            df['statDate'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
                "%Y-%m-%d")
            df['统计日周月'] = df['统计日']

        df['导入时间'] = datetime.datetime.now()

        # 解决可能出现的超时问题bugfix 2020.08.18
        conn.connection.connection.ping(reconnect=True)
        df.to_sql(name='淘宝_生意参谋_市场_市场排行_店铺', con=conn, if_exists='append', index=False)
        # 更新这个类目已经完成(万一中断了，还可以进行断点续传)
        engine.execute(
            " update 平台_类目表 set taobao_sycm_scph_dianpu='1' where cat1='" + row['cat1'] + "' and cat2='" + row[
                'cat2'] + "' and catLeaf='" + row['catLeaf'] + "' and channel='淘宝' ")

        # 淘宝的，需要特别谨慎（分了10秒出去，到菜单点击的页面）
        sleep(75)


def executeCatchWeb(engine, conn):
    try:
        LogTaskAndMachine('淘宝_生意参谋_市场_市场排行_店铺', engine, conn, '', runMode)
        TopAndLeftMainMenu()
        LoopCat(engine, conn)
    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
